﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Common.Views;

namespace DataAccess
{
    public class StatisticsRepository : ConnectionClass
    {
        public IEnumerable<StatisticalReportView> GetProductWithHighestNumberOfFaults()
        {
            IEnumerable<StatisticalReportView> list = (
                           from p in entities.Products
                           join uf in entities.UserFaults
                           on p.ID equals uf.Product_FK
                           group uf by new { p.Name, p.ID } into userfaults

                           select new StatisticalReportView()
                           {
                               productID = userfaults.Key.ID,
                               ProductName = userfaults.Key.Name,
                               mostFaults = userfaults.Select(x => x.FaultID).Distinct().Count()                               
                           }

                           );
            IEnumerable<StatisticalReportView> sorted = list.OrderByDescending(x => x.mostFaults);
            IEnumerable<StatisticalReportView> topTen = sorted.Take(10);

            return topTen;
        }

        public IEnumerable<StatisticalReportView> GetProductWithLeastNumberOfFaults()
        {
            IEnumerable<StatisticalReportView> list = (
                           from p in entities.Products
                           join uf in entities.UserFaults
                           on p.ID equals uf.Product_FK
                           group uf by new { p.Name, p.ID } into userfaults

                           select new StatisticalReportView()
                           {
                               productID = userfaults.Key.ID,
                               ProductName = userfaults.Key.Name,
                               leastFaults = userfaults.Select(x => x.FaultID).Distinct().Count()  
                           }

                           );
            IEnumerable<StatisticalReportView> sorted = list.OrderBy(x => x.leastFaults);
            IEnumerable<StatisticalReportView> topTen = sorted.Take(10);
            
            return topTen;
        }

        public IEnumerable<StatisticalReportView> GetMostPurchasedProduct()
        {
            IEnumerable<StatisticalReportView> list = (
                from p in entities.Products
                join od in entities.OrderDetails
                on p.ID equals od.Product_FK
                group od by new { p.Name, p.ID } into orderDetail

                select new StatisticalReportView()
                {
                    productID = orderDetail.Key.ID,
                    ProductName = orderDetail.Key.Name,
                    mostPurchased = orderDetail.Sum(x => x.Quantity)
                }

                );
            IEnumerable<StatisticalReportView> sorted = list.OrderByDescending(x => x.mostPurchased);
            IEnumerable<StatisticalReportView> topTen = sorted.Take(10);

            return topTen;
        }

        public IEnumerable<StatisticalReportView> GetTopRatedProducts()
        {
            IEnumerable<StatisticalReportView> list = (
                from p in entities.Products
                join r in entities.Ratings
                on p.ID equals r.Product_FK
                group r by new { p.Name, p.ID } into rating

                select new StatisticalReportView()
                {
                    productID = rating.Key.ID,
                    ProductName = rating.Key.Name,
                    mostRated = rating.Average(x => x.Rating1)
                }

                );
            IEnumerable<StatisticalReportView> sorted = list.OrderByDescending(x => x.mostRated);
            IEnumerable<StatisticalReportView> topTen = sorted.Take(10);

            return topTen;
        }
    }
}
